﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace library
{
    public partial class 借阅记录 : Form
    {
        public MySqlConnection conn = new DBclass().getConn();
        public 借阅记录()
        {
            InitializeComponent();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string bname = textBox1.Text;
            string borrowtime1 = dateTimePicker1.Value.ToString();
            string borrowtime2 = dateTimePicker2.Value.ToString();

            conn.Open();
            string sql = "select uname,borrow_info.bID,book1_info.ISBN,bname,author,press,borrowtime,returntime,act_returntime,if(return_whether=1,'是','否') return_whether from borrow_info,user_info,book_info,book1_info where borrow_info.uID=@uID and borrow_info.uID=user_info.uID and book_info.bID=borrow_info.bID and book_info.ISBN=book1_info.ISBN and bname like @bname and borrowtime between @borrowtime1 and @borrowtime2";

            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Add("@uID", MySqlDbType.Int32).Value = User.uID;
            cmd.Parameters.Add("@bname", MySqlDbType.VarChar).Value = "%" + bname + "%";
            cmd.Parameters.Add("@borrowtime1", MySqlDbType.DateTime).Value = borrowtime1;
            cmd.Parameters.Add("@borrowtime2", MySqlDbType.DateTime).Value = borrowtime2;
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            dataGridView4.AutoGenerateColumns = false;
            dataGridView4.DataSource = dataTable;
            this.dataGridView4.Columns["用户名"].DataPropertyName = dataTable.Columns["uname"].ToString();
            this.dataGridView4.Columns["图书编号"].DataPropertyName = dataTable.Columns["bID"].ToString();
            this.dataGridView4.Columns["ISBN"].DataPropertyName = dataTable.Columns["ISBN"].ToString();
            this.dataGridView4.Columns["图书名称"].DataPropertyName = dataTable.Columns["bname"].ToString();
            this.dataGridView4.Columns["作者"].DataPropertyName = dataTable.Columns["author"].ToString();
            this.dataGridView4.Columns["出版社"].DataPropertyName = dataTable.Columns["press"].ToString();
            this.dataGridView4.Columns["借书时间"].DataPropertyName = dataTable.Columns["borrowtime"].ToString();
            this.dataGridView4.Columns["应还时间"].DataPropertyName = dataTable.Columns["returntime"].ToString();
            this.dataGridView4.Columns["实际还书时间"].DataPropertyName = dataTable.Columns["act_returntime"].ToString();
            this.dataGridView4.Columns["是否已归还"].DataPropertyName = dataTable.Columns["return_whether"].ToString();
            this.dataGridView4.Columns["选择归还图书"].Visible = false;
            this.dataGridView4.Columns["续借"].Visible = false;
            this.dataGridView4.Columns["赔书"].Visible = false;
            conn.Close();
            this.button5.Visible = false;
        }

        private void button13_Click(object sender, EventArgs e)
        {
            conn.Open();
            string sql = "select uname,borrow_info.bID,book1_info.ISBN,bname,author,press,borrowtime,returntime,act_returntime,if(return_whether=1,'是','否') return_whether from borrow_info,user_info,book_info,book1_info where borrow_info.uID=@uID and borrow_info.uID=user_info.uID and book_info.bID=borrow_info.bID and book_info.ISBN=book1_info.ISBN and borrow_info.return_whether=0";

            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Add("@uID", MySqlDbType.Int32).Value = User.uID;
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);
            DataTable dataTable = dataSet.Tables[0];
            dataGridView4.AutoGenerateColumns = false;
            dataGridView4.DataSource = dataTable;
            this.dataGridView4.Columns["用户名"].DataPropertyName = dataTable.Columns["uname"].ToString();
            this.dataGridView4.Columns["图书编号"].DataPropertyName = dataTable.Columns["bID"].ToString();
            this.dataGridView4.Columns["ISBN"].DataPropertyName = dataTable.Columns["ISBN"].ToString();
            this.dataGridView4.Columns["图书名称"].DataPropertyName = dataTable.Columns["bname"].ToString();
            this.dataGridView4.Columns["作者"].DataPropertyName = dataTable.Columns["author"].ToString();
            this.dataGridView4.Columns["出版社"].DataPropertyName = dataTable.Columns["press"].ToString();
            this.dataGridView4.Columns["借书时间"].DataPropertyName = dataTable.Columns["borrowtime"].ToString();
            this.dataGridView4.Columns["应还时间"].DataPropertyName = dataTable.Columns["returntime"].ToString();
            this.dataGridView4.Columns["实际还书时间"].DataPropertyName = dataTable.Columns["act_returntime"].ToString();
            this.dataGridView4.Columns["是否已归还"].DataPropertyName = dataTable.Columns["return_whether"].ToString();
            this.dataGridView4.Columns["选择归还图书"].Visible = true;
            this.dataGridView4.Columns["实际还书时间"].Visible = true;
            this.dataGridView4.Columns["续借"].Visible = true;
            this.dataGridView4.Columns["赔书"].Visible = true;
            this.button5.Visible = true;
            conn.Close();
            if (dataGridView4.Rows.Count > 0)
            {
                dataGridView4.EndEdit();//结束编辑状态
                foreach (DataGridViewRow row in dataGridView4.Rows)
                {
                    row.Cells[0].Value = false;

                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (dataGridView4.Rows.Count > 0)
            {
                dataGridView4.EndEdit();//停止编辑状态
                foreach (DataGridViewRow row in dataGridView4.Rows)
                {
                    row.Cells[0].Value = true;
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (dataGridView4.Rows.Count > 0)
            {
                dataGridView4.EndEdit();//结束编辑状态
                foreach (DataGridViewRow row in dataGridView4.Rows)
                {
                    row.Cells[0].Value = false;

                }
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            conn.Open();
            if (dataGridView4.Rows.Count > 0)
            {
                int j = 0, k = 0,l=0,m=0,n=0;
                for (int i = 0; i < dataGridView4.Rows.Count; i++)
                {
                    DataGridViewCheckBoxCell checkCell = (DataGridViewCheckBoxCell)dataGridView4.Rows[i].Cells[0];
                    Boolean flag = Convert.ToBoolean(checkCell.Value);
                    if (flag)
                    { 
                        string bID = dataGridView4.Rows[i].Cells[2].Value.ToString();
                        int uID = User.uID;
                        string ISBN= dataGridView4.Rows[i].Cells[3].Value.ToString();
                        DateTime returntime;
                        //判断是否超期还书
                        string sql2 = "select * from borrow_info where uID=@uID and bID=@bID and return_whether='0'";
                        MySqlCommand cmd2 = new MySqlCommand(sql2, conn);
                        cmd2.Parameters.Add("@uID", MySqlDbType.Int32).Value = uID;
                        cmd2.Parameters.Add("@bID", MySqlDbType.VarChar).Value = bID;
                        MySqlDataReader reader2 = cmd2.ExecuteReader();
                        if (reader2.Read())
                        {
                            returntime=(DateTime)reader2[4];
                            reader2.Close();
                            if (returntime < DateTime.Now)
                            {
                                string msg = "还书超期，请尽快缴纳罚款";
                                MessageBox.Show(msg);
                                //添加罚金记录
                                string sql6 = "select * from fine_rule where rname='滞纳罚金'";
                                MySqlCommand cmd6 = new MySqlCommand(sql6, conn);
                                MySqlDataReader reader6 = cmd6.ExecuteReader();
                                double mag=0;
                                string fee = "";
                                if (reader6.Read())
                                {
                                    mag = (double)reader6["mag"];
                                    reader6.Close();
                                    TimeSpan c = DateTime.Now - returntime;
                                    fee = (c.TotalDays * mag).ToString("0.00");
                                    string sql7 = "insert into fine_info(uID,fee,fee_reason,pay_whether,bID) values(@uID,@fee,'超期罚款','否',@bID)";
                                    MySqlCommand cmd7 = new MySqlCommand(sql7, conn);
                                    cmd7.Parameters.Add("@uID", MySqlDbType.Int32, 10).Value = User.uID;
                                    cmd7.Parameters.Add("@fee", MySqlDbType.Double).Value = fee;
                                    cmd7.Parameters.Add("@bID", MySqlDbType.Double).Value = bID;
                                    l = cmd7.ExecuteNonQuery();
                                }
                            }
                        }
                        reader2.Close();
                        //修改图书归还状态
                        string sql = "update borrow_info set return_whether='1',act_returntime=now() where uID=@uID and bID=@bID and return_whether='0'";
                        MySqlCommand cmd = new MySqlCommand(sql, conn);
                        cmd.Parameters.Add("@uID", MySqlDbType.Int32).Value = uID;
                        cmd.Parameters.Add("@bID", MySqlDbType.VarChar).Value = bID;
                        j += cmd.ExecuteNonQuery();
                        
                        //修改图书是否被借出
                        string sql1 = "update book_info set borrowed_whether='否' where bID=@bID";
                        MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
                        cmd1.Parameters.Add("@bID", MySqlDbType.VarChar).Value = bID;
                        k += cmd1.ExecuteNonQuery();
                        //为预约这本书的人排序
                        string sql3 = "select * from booking_info where booking_status like '已预约' and ISBN=@ISBN order by booking_time";
                        MySqlCommand cmd3 = new MySqlCommand(sql3, conn);
                        cmd3.Parameters.Add("@ISBN", MySqlDbType.VarChar).Value = ISBN;
                        MySqlDataReader reader3 = cmd3.ExecuteReader();
                        int uID1;
                        if(reader3.HasRows)
                        {
                            if(reader3.Read())
                            {//修改用户提示信息
                                uID1 = (int)reader3[3];
                                reader3.Close();
                                /* string sql4 = "update user_info set tips='您预约的书本已在图书馆，请尽快取走' where uID=@uID";
                                MySqlCommand cmd4 = new MySqlCommand(sql4, conn);
                                cmd4.Parameters.Add("@uID", MySqlDbType.VarChar).Value = uID1;
                                m += cmd4.ExecuteNonQuery();*/
                                //修改预约状态
                                string sql5 = "update booking_info set booking_status='待取走',bID=@bID where uID=@uID and ISBN=@ISBN and booking_status='已预约'";
                                MySqlCommand cmd5 = new MySqlCommand(sql5, conn);
                                cmd5.Parameters.Add("@uID", MySqlDbType.Int32).Value = uID1;
                                cmd5.Parameters.Add("@bID", MySqlDbType.VarChar).Value = bID;
                                cmd5.Parameters.Add("@ISBN", MySqlDbType.VarChar).Value = ISBN;
                                n += cmd5.ExecuteNonQuery();
                            }
                        }
                        dataGridView4.Rows[i].DefaultCellStyle.BackColor = Color.Green;
                    }
                }
                if (j > 0 && k > 0)
                {
                    MessageBox.Show("还书成功");
                }
                conn.Close();
            }
        }

        private void 借阅记录_Load(object sender, EventArgs e)
        {
            conn.Open();
            string sql = "select * from booking_info where uID=@uID and booking_status='待取走'";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.Add("@uID", MySqlDbType.Int32).Value = User.uID;
            MySqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                string msg = reader["booking_status"].ToString();
                if (msg == "待取走")
                {
                    MessageBox.Show("您预约的书本已在图书馆，请尽快取走");
                }
                
            }
            reader.Close();
            conn.Close();
        }

        private void dataGridView4_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewColumn column = dataGridView4.Columns[e.ColumnIndex];
                if (column is DataGridViewButtonColumn)
                {
                    if (e.ColumnIndex == 11)
                    {
                        conn.Open();
                        string bID = dataGridView4.Rows[e.RowIndex].Cells[2].Value.ToString();
                        string status = dataGridView4.Rows[e.RowIndex].Cells[10].Value.ToString();
                        if (status == "否")
                        {
                            string sql = "select * from borrow_info where uID=@uid and bID=@bid and return_whether=0";
                            MySqlCommand cmd = new MySqlCommand(sql, conn);
                            cmd.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                            cmd.Parameters.Add("@bid", MySqlDbType.VarChar, 6).Value = bID;
                            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                            DataSet dataSet = new DataSet();
                            adapter.Fill(dataSet);
                            DataTable dataTable = dataSet.Tables[0];
                            int nums = Convert.ToInt32(dataTable.Rows[0]["renew_nums"]);
                            DateTime returntime = (DateTime)dataTable.Rows[0]["returntime"];
                            string sql2 = "select * from borrow_rule where identity=@identity";
                            MySqlCommand cmd2 = new MySqlCommand(sql2, conn);
                            cmd2.Parameters.Add("@identity", MySqlDbType.VarChar, 10).Value = User.identity;
                            MySqlDataAdapter adapter2 = new MySqlDataAdapter(cmd2);
                            DataSet dataSet2 = new DataSet();
                            adapter2.Fill(dataSet2);
                            DataTable dataTable2 = dataSet2.Tables[0];
                            int rule_nums = Convert.ToInt32(dataTable2.Rows[0]["renew_times"]);
                            int renew = Convert.ToInt32(dataTable2.Rows[0]["renew"]);

                            string sql3 = "select * from borrow_info where uID=@uid and return_whether=0";
                            MySqlCommand cmd3 = new MySqlCommand(sql3, conn);
                            cmd3.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                            MySqlDataAdapter adapter3 = new MySqlDataAdapter(cmd3);
                            DataSet dataSet3 = new DataSet();
                            adapter3.Fill(dataSet3);
                            DataTable dataTable3 = dataSet3.Tables[0];
                            int timeflag = 0;
                            for (int i = 0; i < dataTable3.Rows.Count; i++)
                            {
                                DateTime currentTime = new System.DateTime();
                                currentTime = System.DateTime.Now;
                                DateTime dateTime = (DateTime)dataTable3.Rows[i]["returntime"];
                                if (dateTime < currentTime)
                                {
                                    timeflag = 1;
                                    break;
                                }
                            }

                            string sql4 = "select * from fine_info where uID=@uid and pay_whether='否'";
                            MySqlCommand cmd4 = new MySqlCommand(sql4, conn);
                            cmd4.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                            MySqlDataReader reader = cmd4.ExecuteReader();
                            int fineflag = 0;
                            if (reader.Read())
                            {
                                fineflag = 1;
                            }
                            reader.Close();

                            string sql5 = "select * from book_info where bID=@bid";
                            MySqlCommand cmd5 = new MySqlCommand(sql5, conn);
                            cmd5.Parameters.Add("@bid", MySqlDbType.VarChar, 6).Value = bID;
                            MySqlDataAdapter adapter5 = new MySqlDataAdapter(cmd5);
                            DataSet dataSet5 = new DataSet();
                            adapter5.Fill(dataSet5);
                            DataTable dataTable5 = dataSet5.Tables[0];
                            int borrowedflag = 0;
                            if (dataTable5.Rows[0]["booked_whether"].ToString() == "是")
                            {
                                borrowedflag = 1;
                            }
                            if (timeflag == 1)
                            {
                                MessageBox.Show("您有超期未归还的图书，请先归还");
                            }
                            else if (fineflag == 1)
                            {
                                MessageBox.Show("您有未缴纳的罚款，请先缴纳罚款");
                            }
                            else if (!(nums < rule_nums))
                            {
                                MessageBox.Show("你这本书的续借次数已经用完，无法续借");
                            }
                            else if (borrowedflag == 1)
                            {
                                MessageBox.Show("这本书已经被其他人预约了，您不能再续借了");
                            }
                            else
                            {
                                string sql11 = "update borrow_info set returntime=@returntime,renew_nums=renew_nums+1 where uID=@uid and bID=@bID and return_whether=0";
                                MySqlCommand cmd11 = new MySqlCommand(sql11, conn);
                                cmd11.Parameters.Add("@returntime", MySqlDbType.DateTime).Value = returntime.AddDays(renew);
                                cmd11.Parameters.Add("@bid", MySqlDbType.VarChar,6).Value = dataGridView4.Rows[e.RowIndex].Cells[2].Value.ToString();
                                cmd11.Parameters.Add("@uid", MySqlDbType.Int32).Value = User.uID;
                                int res = cmd11.ExecuteNonQuery();
                                if (res > 0)
                                {
                                    MessageBox.Show("续借成功");
                                    this.Close();
                                }
                            }
                        }
                        else if (status == "是")
                        {
                            MessageBox.Show("不能对已经还了的图书续借");
                        }
                        conn.Close();
                    }
                }
                if (column is DataGridViewCheckBoxColumn)
                {
                    if (bool.Parse(dataGridView4.Rows[e.RowIndex].Cells[0].Value.ToString()))
                        dataGridView4.Rows[e.RowIndex].Cells[0].Value = false;
                    else
                        dataGridView4.Rows[e.RowIndex].Cells[0].Value = true;
                }
            }
        }
    }
}
